﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using CinemaProject;
using CinemaDbLib;

namespace CinemaProject
{
    public class SeatDB
    {
        private Connection connection;
        private RoomDB roomDB;

        public SeatDB()
        {
            connection = new Connection();
            roomDB = new RoomDB();
        }

        public int getColCount(int roomId, int rowId)
        {
            string query = "SELECT MAX(col) FROM XpSeats WHERE roomId =" + roomId
                + " AND row = " + rowId;
            connection.Open();
            SqlDataReader reader = connection.Read(query);

            if (reader.Read())
                return reader.GetInt32(0);
            else
                return -1;
        }

        public int getRowCount(int roomId)
        {
            string query = "SELECT MAX(row) FROM XpSeats WHERE roomId =" +
                roomId;
            connection.Open();
            SqlDataReader reader = connection.Read(query);

            if (reader.Read())
                return reader.GetInt32(0);
            else
                return -1;
        }

        public List<Seat> getSeats()
        {
            return where("");
        }

        public Seat getSeatById(int id)
        {
            return singleWhere("id = " + id);
        }

        public List<Seat> getSeatsByRoom(int roomId)
        {
            return where("roomId = " + roomId);
        }

        private Seat singleWhere(String wClause)
        {
            Seat seat = null;

            string query = "SELECT * FROM XpSeats WHERE " + wClause;
            connection.Open();
            SqlDataReader reader = connection.Read(query);
            if (reader.Read())
            {
                seat = new Seat();
                seat.id = reader.GetInt32(0);
                seat.room = roomDB.getRoomById(reader.GetInt32(1));
                seat.row = reader.GetInt32(2);
                seat.col = reader.GetInt32(3);
            }

            reader.Close();
            connection.Close();

            return seat;
        }

        private List<Seat> where(String wClause)
        {
            List<Seat> seats = new List<Seat>();

            string query;
            if (!wClause.Equals(""))
                query = "SELECT * FROM XpSeats WHERE " + wClause;
            else
                query = "SELECT * FROM XpSeats";
            connection.Open();
            SqlDataReader reader = connection.Read(query);

            while (reader.Read())
            {
                Seat seat = new Seat();
                seat.id = reader.GetInt32(0);
                seat.room = roomDB.getRoomById(reader.GetInt32(1));
                seat.row = reader.GetInt32(2);
                seat.col = reader.GetInt32(3);
                seats.Add(seat);
            }

            reader.Close();
            connection.Close();

            return seats;
        }

        public bool removeSeat(int id)
        {
            string query = "DELETE FROM XpSeats WHERE id=" + id;
            connection.Open();
            int result = connection.Execute(query);
            connection.Close();
            return result == 1;
        }

        public int insertSeat(Seat seat)
        {
            int id = connection.GetMaxId("Seats") + 1;
            string query = @"INSERT INTO XpSeats (id, roomId, row, col) " +
                "VALUES ( " + id + ", " + seat.room.id + ", " + seat.row
                + ", " + seat.col + ")";
            connection.Open();
            int result = connection.Execute(query);
            connection.Close();

            if (result == 1)
                return id;
            return -1;
        }

        public bool updateSeat(Seat seat)
        {
            string query = @"UPDATE Seats SET roomId=" + seat.room.id +
                ", row=" + seat.row + ", col=" + seat.col +
                " WHERE id=" + seat.id;
            connection.Open();
            int result = connection.Execute(query);
            connection.Close();
            return result == 1;
        }
    }
}
